﻿Imports DatabaseAccess.DBUtility

Public Class GZContainerBusiness
#Region "工装流水卡打印相关"
    Public Function GetGZBomPrintInfo(ByVal para As Dictionary(Of String, Object)) As DataSet
        Dim result As DataSet = New DataSet()
        Dim containerId As String = para("ContainerId")
        Dim workflowId As String = para("WorkflowId")

        Dim strSql = <string>
                         SELECT cci.containername,cci.qty,cci.containerid,cci.workflowid,cci.productname,cci.productrevision,u.attributevalue gongyiyuan
                         FROM containercurrentinfo cci
                         LEFT JOIN product p ON p.productid = cci.productid
                         LEFT JOIN userattribute u ON u.parentid = p.productid AND u.userattributename = '工艺编制信息'
                         WHERE p.istopgz = 1 AND cci.containerid = '{0}'
                     </string>.Value

        strSql = String.Format(strSql, containerId)
        '主批次信息
        Dim dtMain = DatabaseHelper.Query_ReturnDatTable(strSql)
        dtMain.TableName = "dtMain"

        If dtMain.Rows.Count = 0 Then
            Return result
        End If

        strSql = <string>
                     SELECT t.subproductname,
                            t.subproductrev,
                            t.requireqty,
                            t.requireqty2,
                            p.materialcode,
                            p.materialtrademark,
                            p.materialspecification,
                            p.materialsize
                        FROM (SELECT pp.subproductname,pp.subproductrev,pp.requireqty,pp.requireqty2
                                FROM bomproductsubproduct pp
                                START WITH pp.productname = '{0}' AND pp.productrev = '{1}'
                            CONNECT BY PRIOR pp.subproductname = pp.productname
                                ORDER BY pp.subproductname) t
                        LEFT JOIN productbase pb ON pb.productname = t.subproductname
                        LEFT JOIN product p ON p.productbaseid = pb.productbaseid AND p.productrevision = t.subproductrev
                        ORDER BY t.subproductname
                 </string>.Value

        strSql = String.Format(strSql, dtMain.Rows(0)("productname"), dtMain.Rows(0)("productrevision"))
        Dim dtBom = DatabaseHelper.Query_ReturnDatTable(strSql)
        dtBom.TableName = "dtBom"

        result.Tables.Add(dtMain)
        result.Tables.Add(dtBom)
        Return result
    End Function

    ''' <summary>
    ''' 工装工艺规程打印
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetGZWorkflowPrintInfo(ByVal para As Dictionary(Of String, Object)) As DataSet
        Dim result As DataSet = New DataSet()
        Dim containerId As String = para("ContainerId")
        Dim workflowId As String = para("WorkflowId")
        Dim beginSequence = "0"
        Dim isBlank As Boolean = para("IsBlank")

        Dim strSql = <string>
        SELECT cci.containername,cci.containerid,cci.realcontainerno,cci.qty,cci.productname,cci.productrevision,cci.productdescription,
        p.materialcode,p.materialtrademark,p.materialspecification,fws.sequence beginsequence,c.plannedqty2 originalqty2,p.materialsize,
        ut2.attributevalue gongyiyuan,ut3.attributevalue jiaoduiyuan,cci.materialdate,w.process_no,ct.lastchangedate,w.workflowrevision,w.mlsl, wi.*
        FROM containercurrentinfo cci
        LEFT JOIN product p ON cci.productid = p.productid
        LEFT JOIN container c ON c.containerid = cci.containerid
        LEFT JOIN userattribute ut ON ut.parentid=cci.containerid AND ut.userattributename='BeginStep' 
        LEFT JOIN workflowstep fws ON fws.workflowstepname=ut.attributevalue AND fws.workflowid='{1}'
        LEFT JOIN userattribute ut2 ON ut2.parentid = cci.containerid AND ut2.userattributename = '工艺编制信息'
        LEFT JOIN userattribute ut3 ON ut3.parentid = cci.containerid AND ut3.userattributename = '工艺校对信息'
        LEFT JOIN workflow w ON w.workflowid = '{1}'
        LEFT JOIN workflowsignatureinfo wi ON wi.workflowid = w.workflowid
        LEFT JOIN CHANGESTATUS ct ON ct.parentid = w.workflowid 
        WHERE  cci.containerid = '{0}' </string>.Value

        strSql = String.Format(strSql, containerId, workflowId)
        '批次基本信息
        Dim dtMain = DatabaseHelper.Query_ReturnDatTable(strSql)
        dtMain.TableName = "dtMain"
        result.Tables.Add(dtMain)

        If dtMain.Rows.Count = 0 Then
            Return result
        Else
            'If Not String.IsNullOrWhiteSpace(dtMain.Rows(0)("beginSequence").ToString) Then
            '    beginSequence = dtMain.Rows(0)("beginSequence").ToString
            'End If
        End If

        '查询所有加工信息
        Dim dtContainerStep As New DataTable
        dtContainerStep = DatabaseHelper.Query_ReturnDatTable(GetWorkflowStepForPrint(workflowId, beginSequence)) '工艺下的工序信息

        If isBlank = False Then
            Dim dtContainerStepWorkRecord = DatabaseHelper.Query_ReturnDatTable(GetContainerStepWorkingRecord(containerId, "")) '派报检信息
            dtContainerStepWorkRecord.AcceptChanges()
            Dim curContainerStepWorkRecord As DataTable = New DataTable '当前所查工艺的所有加工记录
            Dim drs = dtContainerStepWorkRecord.Select(String.Format("workflowid='{0}'", workflowId))
            If drs.Length > 0 Then
                curContainerStepWorkRecord = drs.CopyToDataTable
            End If
            Dim delNum = 0
            Dim temDt = dtContainerStep.Copy
            For i As Integer = 0 To temDt.Rows.Count - 1
                If dtContainerStepWorkRecord.Compute("count(containerid)", "workflowstepname='" + temDt.Rows(i)("workflowstepname").ToString + "'") > 0 Then '已有加工记录时移除空白工序
                    dtContainerStep.Rows.RemoveAt(i - delNum)
                    delNum += 1
                End If
            Next
            dtContainerStep.Merge(dtContainerStepWorkRecord) '加上当前所有工序及加工记录
            dtContainerStep.AcceptChanges()
            dtContainerStep.DefaultView.Sort = " specno asc"
            dtContainerStep = dtContainerStep.DefaultView.ToTable
            'dtContainerStep = GetContainerStepMerge(dtContainerStep) '处理其多条相同记录的工序 add by zhangrj 2021-12-10
        Else
            'dtContainerStep = GetContainerStepMerge(dtContainerStep) '处理其多条相同记录的工序 add by zhangrj 2021-12-10
        End If
        dtContainerStep.Columns.Add("StepNotes")
        '批次工序属性信息 add zhangrj 2022-12-06
        Dim dtContainerStepAttributes = New DataTable()
        strSql = "SELECT csa.workflowstepid,wm_concat(csa.notes) notes FROM containerstepattribute csa WHERE csa.containerid = '{0}' GROUP BY csa.workflowstepid"
        dtContainerStepAttributes = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, containerId))
        '批次工序外委信息 add zhangrj 2023-07-05
        Dim dtCommissioning = New DataTable()
        strSql = String.Format(<string>
                    SELECT cm.workflowstepid,cm.workflowstepname,ct.customername FROM containercommissioning cc
                    LEFT JOIN customer ct ON ct.customerid = cc.outcustomerid
                    LEFT JOIN containercommidetail cm ON cm.containercommissioningid = cc.containercommissioningid 
                    WHERE cc.containerid = '{0}' AND cc.isuse = 0 AND cc.isuse = 0
                 </string>.Value, containerId)
        dtCommissioning = DatabaseHelper.Query_ReturnDatTable(strSql)
        '统一处理工序信息
        For Each dr As DataRow In dtContainerStep.Rows
            '外委序将外委厂家赋值到报工人、检验人
            Dim drCommi As DataRow = dtCommissioning.Select(String.Format("workflowstepid='{0}'", dr("workflowstepid").ToString)).FirstOrDefault()
            If Not drCommi Is Nothing Then
                dr("reportemployeename") = drCommi("customername").ToString()
                dr("checkemployeename") = drCommi("customername").ToString()
            End If
            '将工序备注赋值到工序上
            Dim drAttr As DataRow = dtContainerStepAttributes.Select(String.Format("workflowstepid='{0}'", dr("workflowstepid").ToString)).FirstOrDefault()
            If Not drAttr Is Nothing Then
                dr("StepNotes") = drAttr("notes").ToString()
            End If
        Next
        dtContainerStep.AcceptChanges()
        dtContainerStep.TableName = "dtContainerStep"
        result.Tables.Add(dtContainerStep)
        Return result
    End Function

    ''' <summary>
    ''' 获取待打印的工序信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetWorkflowStepForPrint(ByVal workflowid, ByVal beginSequence) As String
        Dim strSql As String = <string>
SELECT w.workflowid,wb.workflowname,ws.workflowstepid,ws.workflowstepname,sb1.specname,s.specid,s.iskey,ws.sequence,ws.subworkflowid,ws.subworkflowbaseid,
       s.opdesc,nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno,
       NVL(ws.controlcode,s.controlcode) controlcode,w.process_no,
       ws.steptype,sd.durationperunit,ws.description
FROM workflow w 
LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
left join specbase sb on sb.specbaseid = ws.specbaseid 
left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid) 
left join specbase sb1 on sb1.specbaseid = s.specbaseid 
WHERE (NVL(ws.controlcode,s.controlcode)!='LY07' OR NVL(ws.controlcode,s.controlcode) IS NULL) 
                              </string>
        strSql += String.Format(" AND w.workflowid='{0}'", workflowid)
        If Not String.IsNullOrWhiteSpace(beginSequence) Then
            strSql += String.Format(" and ws.sequence>={0}", beginSequence)
        End If
        strSql += " ORDER BY ws.sequence ASC"
        GetWorkflowStepForPrint = strSql
    End Function

    ''' <summary>
    ''' 查询对应批次和工艺的加工记录
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetContainerStepWorkingRecord(ByVal containerID As String, ByVal workflowID As String) As String
        Dim strSql As String = <string>
SELECT  csr.containerid,csr.workflowid,csr.workflowstepid,ws.WORKFLOWSTEPname,sb.specname,csr.specid,s.iskey,csr.ReportQty,csr.ReportEmployeeName,
        csr.reportemployeeid,(CASE WHEN csr.reportemployeeid IS NULL OR csr.reportemployeeid=csr.checkemployeeid THEN csr.checkdate ELSE csr.reportdate END) reportdate,
        csw.checktype,csr.checkemployeeid,csr.checkemployeename,csr.passqty,csr.lossqty,csr.defectqty,csr.usecompleted,ws.sequence,s.opdesc,
        nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno,csw.containerstepworkreportid reportid,
        csw.firstcheckcontainerno,csw.notes worknote,k.checknote,NVL(ws.controlcode,s.controlcode) controlcode,
        s.description,sd.durationperunit
FROM containersteprecord csr 
left join containerstepworkreport csw on csw.containerstepworkreportid=csr.containersteprecordid and csw.isuse = 0 
left join
--新增检验数据 add by zhangrj
(SELECT cci.containerstepworkreportid,wm_concat(cci.checknote) checknote FROM containercheckinfo cci 
WHERE cci.isuse = 0 AND cci.containerid = '{0}' 
GROUP BY cci.containerstepworkreportid)k ON k.containerstepworkreportid = csw.containerstepworkreportid
--
left join spec s ON s.specid=CSR.SPECID
left join specbase sb on sb.specbaseid=s.specbaseid
LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CSR.WORKFLOWSTEPID
LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
WHERE (NVL(ws.controlcode,s.controlcode)!='LY07' OR NVL(ws.controlcode,s.controlcode) IS NULL) AND csr.Reportdate IS NOT NULL AND csr.usefulstatus=1 
and csr.containerid='{0}'
                               </string>
        strSql = String.Format(strSql, containerID)
        If Not String.IsNullOrWhiteSpace(workflowID) Then
            strSql += String.Format(" and csr.workflowid='{0}'", workflowID)
        End If
        GetContainerStepWorkingRecord = strSql
    End Function

    ''' <summary>
    ''' 处理同一个工序有相同检验员和相同报工人的数量有多条 add by zhangrj 2021-12-10
    ''' </summary>
    ''' <param name="dtSourceContainerStep"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerStepMerge(ByVal dtSourceContainerStep As DataTable) As DataTable
        If Not dtSourceContainerStep.Columns.Contains("reportemployeeid") Then
            Return dtSourceContainerStep
        End If
        Dim dtNewContainerStep As DataTable = dtSourceContainerStep.Clone
        Dim clength = dtSourceContainerStep.Columns.Count
        While dtSourceContainerStep.Rows.Count > 0
            Dim item = dtSourceContainerStep.Rows(0)
            Dim rows = dtSourceContainerStep.Select(String.Format("workflowstepid = '{0}' and reportemployeeid='{1}' and checkemployeeid = '{2}'",
                                                                item("workflowstepid").ToString(), item("reportemployeeid").ToString(), item("checkemployeeid").ToString()))
            If rows.Length > 1 Then '如果同一个工序有相同检验员和相同报工人的数量有多条，则合并处理'
                Dim newDr As DataRow = rows(0)
                Dim reportDate As DateTime = DateTime.Parse(rows(0)("reportdate").ToString())
                Dim reportQty As Integer = Integer.Parse(rows(0)("ReportQty").ToString())
                Dim passqty As Integer = Integer.Parse(rows(0)("passqty").ToString())
                Dim lossqty As Integer = Integer.Parse(rows(0)("lossqty").ToString())
                Dim firstCheckNo = ""
                If rows.CopyToDataTable.Select("firstcheckcontainerno is not null").Length > 0 Then '首件批次号 add:wangjh 20220114
                    firstCheckNo = rows.CopyToDataTable.Select("firstcheckcontainerno is not null")(0)("firstcheckcontainerno")
                End If
                Dim defectqty As Integer = 0
                If Not String.IsNullOrWhiteSpace(rows(0)("defectqty").ToString()) Then
                    defectqty = Integer.Parse(rows(0)("defectqty").ToString())
                End If
                'Dim defectqty As Integer = IIf(String.IsNullOrWhiteSpace(rows(0)("defectqty").ToString()), 0, Integer.Parse(rows(0)("defectqty").ToString()))
                dtSourceContainerStep.Rows.Remove(rows(0))
                For index = 1 To rows.Length - 1 '从第二行开始。循环每一行'
                    Dim dr = rows(index)
                    If reportDate < DateTime.Parse(dr("reportdate").ToString()) Then
                        reportDate = DateTime.Parse(dr("reportdate").ToString())
                    End If
                    reportQty += Integer.Parse(dr("ReportQty").ToString())
                    passqty += Integer.Parse(dr("passqty").ToString())
                    lossqty += Integer.Parse(dr("lossqty").ToString())
                    If Not String.IsNullOrWhiteSpace(dr("defectqty").ToString()) Then
                        defectqty += Integer.Parse(dr("defectqty").ToString())
                    End If
                    'defectqty += IIf(String.IsNullOrWhiteSpace(dr("defectqty").ToString()), 0, Integer.Parse(dr("defectqty").ToString()))
                    For j = 0 To clength - 1 '循环每一列'
                        If Not String.IsNullOrWhiteSpace(dr(j).ToString()) Then
                            newDr(j) = dr(j)
                        End If
                    Next
                    dtSourceContainerStep.Rows.Remove(rows(index))
                Next
                newDr("reportdate") = reportDate
                newDr("ReportQty") = IIf(reportQty = 0, DBNull.Value, reportQty) '如果为0，则不显示出来'
                newDr("passqty") = IIf(passqty = 0, DBNull.Value, passqty)
                newDr("lossqty") = IIf(lossqty = 0, DBNull.Value, lossqty)
                newDr("defectqty") = IIf(defectqty = 0, DBNull.Value, defectqty)
                newDr("firstcheckcontainerno") = firstCheckNo '首件批次号 add:wangjh 20220114
                dtNewContainerStep.Rows.Add(newDr.ItemArray)
            Else
                If String.IsNullOrWhiteSpace(item("checkemployeeid").ToString()) Then '如果还没有检验，则不显示详细数据'
                    item("ReportQty") = DBNull.Value
                    item("passqty") = DBNull.Value
                    item("lossqty") = DBNull.Value
                    item("defectqty") = DBNull.Value
                    item("reportdate") = DBNull.Value
                    item("ReportEmployeeName") = ""
                    item("firstcheckcontainerno") = ""
                    item("checkemployeename") = ""
                    If dtNewContainerStep.Select(String.Format("workflowstepid = '{0}'", item("workflowstepid").ToString())).Length = 0 Then '如果没有检验并且还没添加到新表中，则添加'
                        dtNewContainerStep.Rows.Add(item.ItemArray)
                    End If
                Else
                    dtNewContainerStep.Rows.Add(item.ItemArray)
                End If
                dtSourceContainerStep.Rows.Remove(item)
            End If
        End While
        Return dtNewContainerStep
    End Function

    ''' <summary>
    ''' 获取工装批次(验证打印)
    ''' </summary>
    ''' <param name="containerIds"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetGZContainerInfo(ByVal containerIds As String) As DataTable
        Dim strSql = <string>
                   SELECT  c.containerid,c.containername FROM Container c     
  LEFT JOIN Factory f ON f.factoryid = c.factoryid                                     
  WHERE c.containerid IN {0} AND f.factoryname like '%工装工具分公司%'
                     </string>.Value
        strSql = String.Format(strSql, containerIds)
        Return DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function

#End Region
End Class
